import { AsColumn, Column, ColumnType } from '../schema/Column';
import { Table } from '../schema/Table';
import { Internal } from './Internal';
import { ValueType } from '@kit.ArkData';

export type ExecuteSQL = SQLiteExecuteDsl

export type QuerySQL<T extends ColumnType[]> = DataQueryLanguage<T> | Pick<DataQueryLanguage<T>, any>

export class SQL {
  private constructor() {
  }

  /**
   * 同时构建CREATE TABLE和CREATE INDEX语句
   *
   * @param table 目标表
   * @param ifNotExists 是否添加IF NOT EXISTS条件
   */
  static createTableAndIndex(table: Table<any>, ifNotExists: boolean = false) {
    return new SQLiteExecuteDsl().createTableAndIndex(table, ifNotExists)
  }

  /**
   * 开始构建CREATE TABLE语句
   *
   * @param table 目标表
   * @param ifNotExists 是否添加IF NOT EXISTS条件
   */
  static createTable(table: Table<any>, ifNotExists: boolean = false) {
    return new SQLiteExecuteDsl().createTable(table, ifNotExists)
  }

  /**
   * 生成CREATE INDEX语句
   *
   * @param table 目标表
   * @param ifNotExists 是否添加IF NOT EXISTS条件
   */
  static createIndex(table: Table<any>, ifNotExists: boolean = false) {
    return new SQLiteExecuteDsl().createIndex(table, ifNotExists)
  }

  /**
   * 开始构建ALTER TABLE语句
   *
   * @param table 目标表
   * @param scope 修改的范围
   */
  static alterTable(table: Table<any>, scope: (sql: AlterTableScope) => void) {
    return new SQLiteExecuteDsl().alterTable(table, scope)
  }

  /**
   * 开始构建SELECT语句
   *
   * @param columns 选择的列
   */
  static select<T extends ColumnType[]>(...columns: T) {
    return new DataQueryLanguage().select(...columns)
  }
}

function buildColumnModifier(column: ColumnType) {
  let modifier = `${column.fieldName} ${column.dataType}`
  if (column.isNotNull) {
    modifier += ' NOT NULL'
  }
  if (column.isUnique) {
    modifier += ' UNIQUE'
  }
  if (column.isPrimaryKey) {
    modifier += ' PRIMARY KEY'
  }
  if (column.isAutoincrement) {
    modifier += ' AUTOINCREMENT'
  }
  if (column.defaultStr) {
    modifier += ` DEFAULT '${column.defaultStr}'`
  }
  return modifier
}

function buildCreateTable(table: Table<any>, ifNotExists: boolean) {
  const columnArgs = table[Internal.TableOptions].columns
    .map(column => buildColumnModifier(column))
    .join(', ')
  return `CREATE TABLE ${ifNotExists ? 'IF NOT EXISTS' : ''} ${table.tableName} (${columnArgs});`
}

function buildCreateIndex(table: Table<any>, ifNotExists: boolean = false) {
  return table[Internal.TableOptions].indexColumns
    .map(indexColumn => {
      const columnArgs = indexColumn
        .columns
        .map(([column, order]) => `${column.fieldName}${order ? ` ${order}` : ''}`)
        .join(',')
      return `CREATE ${indexColumn.isUnique ? 'UNIQUE' : ''} INDEX ${ifNotExists ? 'IF NOT EXISTS' :
        ''} ${indexColumn.fieldName} ON ${table.tableName}(${columnArgs});`
    })
    .join('')
}

/**
 * DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等
 */
class SQLiteExecuteDsl implements Object {
  constructor(private commands = '') {
  }

  /**
   * 同时构建CREATE TABLE和CREATE INDEX语句
   *
   * @param table 目标表
   * @param ifNotExists 是否添加IF NOT EXISTS条件
   */
  createTableAndIndex(table: Table<any>, ifNotExists: boolean = false): this {
    this.createTable(table, ifNotExists)
    this.createIndex(table, ifNotExists)
    return this
  }

  /**
   * 开始构建CREATE TABLE语句
   *
   * @param table 目标表
   * @param ifNotExists 是否添加IF NOT EXISTS条件
   */
  createTable(table: Table<any>, ifNotExists: boolean = false): this {
    this.commands += buildCreateTable(table, ifNotExists)
    return this
  }

  /**
   * 生成CREATE INDEX语句
   *
   * @param table 目标表
   * @param ifNotExists 是否添加IF NOT EXISTS条件
   */
  createIndex(table: Table<any>, ifNotExists: boolean = false): this {
    this.commands += buildCreateIndex(table, ifNotExists)
    return this
  }

  /**
   * 生成DROP Table语句
   *
   * @param table 目标表
   * @param ifNotExists 是否添加IF NOT EXISTS条件
   */
  dropTable(table: Table<any>, ifNotExists: boolean = false): this {
    this.commands += `DROP TABLE ${ifNotExists ? 'IF EXISTS' : ''} ${table.tableName};`
    return this
  }

  /**
   * 开始构建ALTER TABLE语句
   *
   * @param table 目标表
   * @param scope 修改的范围
   */
  alterTable(
    table: Table<any>,
    scope: (sql: AlterTableScope) => void): this {
    const alterTableScope: AlterTableScope = {
      addColumn: (column) => {
        this.commands += `ALTER TABLE ${table.tableName} ADD COLUMN ${buildColumnModifier(column)};`
        return alterTableScope
      },
      alterColumn: (column) => {
        this.commands += `ALTER TABLE ${table.tableName} ALTER COLUMN ${buildColumnModifier(column)};`
        return alterTableScope
      }
    }
    scope(alterTableScope)
    return this
  }

  /**
   * 获取最终生成的SQL语句
   *
   * @returns 拼接完成的SQL命令
   */
  toString(): string {
    return this.commands
  }
}

interface AlterTableScope {
  /**
   * 对应ADD COLUMN语句
   *
   * @param column 目标列
   */
  addColumn(column: ColumnType): this

  /**
   * 对应ALTER COLUMN语句
   *
   * @param column 目标列
   */
  alterColumn(column: ColumnType): this
}

class DataQueryLanguage<T extends ColumnType[]> implements Object {
  constructor(private command = '') {
  }

  /**
   * 指定查询的目标表
   *
   * @param targetTable 目标表
   */
  form(targetTable: Table<any>): Pick<DataQueryLanguage<T>, 'innerJoin' | 'leftJoin' | 'where'> {
    this.command += `FROM ${targetTable.tableName}`
    this.command += ' '
    return this
  }

  /**
   * 开始构建SELECT语句
   *
   * @param columns 选择的列
   */
  select<T extends ColumnType[]>(...columns: T): Pick<DataQueryLanguage<T>, 'form'> {
    this.command += `SELECT ${columns.map(item => {
      if
      (item instanceof AsColumn)
      {
        return`${item.tableName}.${item.fieldName} AS ${item.asFieldName}`
      }
      return`${item.tableName}.${item.fieldName}`
    }).join(',')}`
    this.command += ' '
    return this
  }

  /**
   * 开始构建SELECT DISTINCT语句
   *
   * @param columns 选择的列
   */
  selectDistinct<T extends ColumnType[]>(...columns: T): Pick<DataQueryLanguage<T>, 'form'> {
    this.command += `SELECT DISTINCT${columns.map(item => {
      if
      (item instanceof AsColumn)
      {
        return`${item.tableName}.${item.fieldName} AS ${item.asFieldName}`
      }
      return`${item.tableName}.${item.fieldName}`
    }).join(',')}`
    this.command += ' '
    return this
  }

  /**
   * 添加INNER JOIN子句
   *
   * @param targetTable 目标表
   */
  innerJoin(targetTable: Table<any>): Pick<DataQueryLanguage<T>, 'where'> {
    this.command += `INNER JOIN ${targetTable.tableName}`
    this.command += ' '
    return this
  }

  /**
   * 添加LEFT JOIN子句
   *
   * @param targetTable 目标表
   */
  leftJoin(targetTable: Table<any>): Pick<DataQueryLanguage<T>, 'where'> {
    this.command += `LEFT JOIN ${targetTable.tableName}`
    this.command += ' '
    return this
  }

  /**
   * 添加RIGHT JOIN子句
   *
   * @param targetTable 目标表
   */
  rightJoin(targetTable: Table<any>): Pick<DataQueryLanguage<T>, 'where'> {
    this.command += `RIGHT JOIN ${targetTable.tableName}`
    this.command += ' '
    return this
  }

  where(): Pick<DataQueryLanguage<T>, 'eq' | 'notEq' | 'less' | 'lessEq' | 'greater' | 'greaterEq' | 'in' | 'isNull' | 'isNoNull' | 'groupBy'> {
    this.command += `WHERE`
    this.command += ' '
    return this
  }

  and(): Pick<DataQueryLanguage<T>, 'eq' | 'notEq' | 'less' | 'lessEq' | 'greater' | 'greaterEq' | 'in' | 'isNull' | 'isNoNull' | 'groupBy'> {
    this.command += `AND`
    this.command += ' '
    return this
  }

  or(): Pick<DataQueryLanguage<T>, 'eq' | 'notEq' | 'less' | 'lessEq' | 'greater' | 'greaterEq' | 'in' | 'isNull' | 'isNoNull' | 'groupBy'> {
    this.command += `OR`
    this.command += ' '
    return this
  }

  on(): Pick<DataQueryLanguage<T>, 'eq' | 'notEq' | 'less' | 'lessEq' | 'greater' | 'greaterEq' | 'in' | 'isNull' | 'isNoNull' | 'groupBy'> {
    this.command += `ON`
    this.command += ' '
    return this
  }

  /**
   * 添加NOT NULL条件
   *
   * @param column 目标列
   */
  isNoNull(column: ColumnType): Pick<DataQueryLanguage<T>, 'and' | 'or' | 'groupBy'> {
    this.command += `${column.tableName}.${column.fieldName} is not null`
    this.command += ' '
    return this
  }

  /**
   * 添加IS NULL条件
   *
   * @param column 目标列
   */
  isNull(column: ColumnType): Pick<DataQueryLanguage<T>, 'and' | 'or' | 'groupBy'> {
    this.command += `${column.tableName}.${column.fieldName} is null`
    this.command += ' '
    return this
  }

  /**
   * 向查询中添加等于条件
   *
   * @param column 目标列
   * @param value 比较值
   */
  eq(column: ColumnType, value: ColumnType | ValueType): Pick<DataQueryLanguage<T>, 'and' | 'or' | 'groupBy'> {
    if (value instanceof Column) {
      this.command += `${column.tableName}.${column.fieldName} = ${value.tableName}.${value.fieldName}`
    } else {
      this.command += `${column.tableName}.${column.fieldName} = '${value}'`
    }
    this.command += ' '
    return this
  }

  /**
   * 添加不等于条件
   *
   * @param column 目标列
   * @param value 比较值（可以是另一列或字面量值）
   * @returns 返回可链式调用的 AND/OR 方法
   */
  notEq(column: ColumnType, value: ColumnType | ValueType): Pick<DataQueryLanguage<T>, 'and' | 'or' | 'groupBy'> {
    if (value instanceof Column) {
      this.command += `${column.tableName}.${column.fieldName} != ${value.tableName}.${value.fieldName}`
    } else {
      this.command += `${column.tableName}.${column.fieldName} != '${value}'`
    }
    this.command += ' '
    return this
  }

  /**
   * 添加小于条件
   *
   * @param column 目标列
   * @param value 比较值（可以是另一列或字面量值）
   * @returns 返回可链式调用的 AND/OR 方法
   */
  less(column: ColumnType, value: ColumnType | ValueType): Pick<DataQueryLanguage<T>, 'and' | 'or' | 'groupBy'> {
    if (value instanceof Column) {
      this.command += `${column.tableName}.${column.fieldName} < ${value.tableName}.${value.fieldName}`
    } else {
      this.command += `${column.tableName}.${column.fieldName} < '${value}'`
    }
    this.command += ' '
    return this
  }

  /**
   * 添加小于等于条件
   *
   * @param column 目标列
   * @param value 比较值（可以是另一列或字面量值）
   */
  lessEq(column: ColumnType, value: ColumnType | ValueType): Pick<DataQueryLanguage<T>, 'and' | 'or' | 'groupBy'> {
    if (value instanceof Column) {
      this.command += `${column.tableName}.${column.fieldName} <= ${value.tableName}.${value.fieldName}`
    } else {
      this.command += `${column.tableName}.${column.fieldName} <= '${value}'`
    }
    this.command += ' '
    return this
  }

  /**
   * 添加大于条件
   *
   * @param column 目标列
   * @param value 比较值（可以是另一列或字面量值）
   * @returns 返回可链式调用的 AND/OR 方法
   */
  greater(column: ColumnType, value: ColumnType | ValueType): Pick<DataQueryLanguage<T>, 'and' | 'or' | 'groupBy'> {
    if (value instanceof Column) {
      this.command += `${column.tableName}.${column.fieldName} > ${value.tableName}.${value.fieldName}`
    } else {
      this.command += `${column.tableName}.${column.fieldName} > '${value}'`
    }
    this.command += ' '
    return this
  }

  /**
   * 添加大于等于条件
   *
   * @param column 目标列
   * @param value 比较值（可以是另一列或字面量值）
   */
  greaterEq(column: ColumnType, value: ColumnType | ValueType): Pick<DataQueryLanguage<T>, 'and' | 'or' | 'groupBy'> {
    if (value instanceof Column) {
      this.command += `${column.tableName}.${column.fieldName} >= ${value.tableName}.${value.fieldName}`
    } else {
      this.command += `${column.tableName}.${column.fieldName} >= '${value}'`
    }
    this.command += ' '
    return this
  }

  /**
   * 添加 IN 条件
   * @param column 目标列
   * @param values 匹配的值列表
   */
  in(column: ColumnType, values: ValueType[]): Pick<DataQueryLanguage<T>, 'and' | 'or' | 'groupBy'> {
    const quotedValues = values.map(v => `'${v}'`).join(',');
    this.command += `${column.tableName}.${column.fieldName} IN (${quotedValues})`;
    this.command += ' ';
    return this;
  }

  groupBy(column: ColumnType): Pick<DataQueryLanguage<T>, 'and' | 'or' | 'groupBy'> {
    this.command += `GROUP BY ${column.tableName}.${column.fieldName}`
    this.command += ' '
    return this
  }

  /**
   * 获取最终生成的SQL查询
   *
   * @returns 拼接完成的SQL命令
   */
  toString(): string {
    return this.command
  }
}